﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Web_Oto.Bases;
using Web_Oto.Data.Dto;

namespace Web_Oto.Data
{
    public class CategoryDal : dalObject
    {
        #region constructors

        public CategoryDal(string connectionstring, string connectiontype)
            : base(connectionstring, connectiontype)
        {

        }

        public CategoryDal(IDbConnection sqlConnection, string connectiontype, SqlTransaction dbTransaction)
            : base(sqlConnection, connectiontype, dbTransaction)
        {

        }

        #endregion

        #region methods

        public IList<Category> GetCategories()
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Categories.ToList();
            }
        }

        public Category GetCategoryById(int id)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Categories.FirstOrDefault(c => c.Id == id);
            }
        }

        public List<CategoryDto> GetCategoryDtos(string name = "", int parentId = 0)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var categoryDtos = db.Categories
                    .Where(p => (p.Name.Contains(name) || string.IsNullOrEmpty(name))
                    && (p.ParentId == parentId || parentId == 0 || p.Id == parentId))
                    .Select(p => new CategoryDto
                                     {
                                         Id = p.Id,
                                         Name = p.Name,
                                         ParentId = p.ParentId ?? 0,
                                         SEO_Title = p.SEO_Title,
                                         SEO_Keyword = p.SEO_Keyword,
                                         SEO_Description = p.SEO_Description
                                     }).ToList();

                foreach (var categoryDto in categoryDtos)
                {
                    if (categoryDto.ParentId > 0)
                    {
                        var firstOrDefault = categoryDtos.FirstOrDefault(p => p.Id == categoryDto.ParentId);
                        if (firstOrDefault != null)
                            categoryDto.ParentName = firstOrDefault.Name;
                    }
                }
                return categoryDtos;
            }
        }

        public int Delete(int id)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {
                    var category = db.Categories.FirstOrDefault(p => p.Id == id);
                    if (category != null)
                    {
                        db.Categories.DeleteOnSubmit(category);
                        db.SubmitChanges();
                    }
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public int Update(Category category)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {
                    var categoryDb = db.Categories.FirstOrDefault(p => p.Id == category.Id);
                    if (categoryDb != null)
                    {
                        categoryDb.Name = category.Name;
                        categoryDb.ParentId = category.ParentId;
                        categoryDb.SEO_Title = category.SEO_Title;
                        categoryDb.SEO_Keyword = category.SEO_Keyword;
                        categoryDb.SEO_Description = category.SEO_Description;
                        db.SubmitChanges();
                    }
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public List<Category> GetCategoryParents()
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Categories.Where(p => p.ParentId == null).ToList();
            }
        }

        public int Insert(Category category)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {                    
                    db.Categories.InsertOnSubmit(category);
                    db.SubmitChanges();
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public IList<Category> GetCategoryByParentId(int? parentId)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                if (parentId == null)
                    return db.Categories.Where(c => c.ParentId == null || c.ParentId == 0).ToList();
                return db.Categories.Where(c => c.ParentId == parentId).ToList();
            }
        }

        public List<Category> GetChildrenCategoryDtos()
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Categories.Where(p => p.ParentId > 0).ToList();
                    //.Select(p => new CategoryDto
                    //                 {
                    //                     Id = p.Id,
                    //                     Name = p.Name,
                    //                     ParentId = p.ParentId ?? 0
                    //                 }).ToList();
                //var parentCategories = db.Categories.Where(p => p.ParentId == null).ToList();
                //foreach (var categoryDto in categoryDtos)
                //{
                //    var firstOrDefault = parentCategories.FirstOrDefault(p => p.Id == categoryDto.ParentId);
                //    if (firstOrDefault != null)
                //        categoryDto.ParentName = firstOrDefault.Name;
                //}
                //return categoryDtos;
            }
        }

        public IList<Category> GetSubCategories()
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Categories.Where(c => c.ParentId != null).ToList();
            }
        }

        #endregion
    }
}
